USE [BMIG_MENSUAL_POST_CADENA]

 -- creating the store procedure
IF EXISTS (SELECT name 
	   FROM   sysobjects 
	   WHERE  name = 'WASP_M0009_DESCRIPCIONES'
	   AND 	  type = 'P') 
	DROP PROCEDURE [dbo].[WASP_M0009_DESCRIPCIONES]
GO

CREATE PROCEDURE [dbo].[WASP_M0009_DESCRIPCIONES] AS
begin
    select DISTINCT
    	a.TipoRegistro
    , Cast(a.PeriodoPro as varchar) as PeriodoPro 
    , a.FechaEnvio
    ,	Cast(a.NroCorrelativoArchivo as varchar)as NroCorrelativoArchivo
    ,	Cast(a.TipoMovimiento as varchar) as TipoMovimiento
    ,	Cast(a.NroProducto as varchar) as NroProducto
    ,	Cast(a.NroPoliza as varchar) as NroPoliza
    ,	a.CODAGEN
    ,	Cast(a.NroCredito as varchar) as NroCredito
    ,	Cast(a.RutAsegurado as varchar) as RutAsegurado
    ,	a.[DV Rut Asegurado]
    ,	a.[Nombre Asegurado]
    ,	a.DireccionAsegurado
    ,	Cast(a.TelefonoAsegurado as varchar) as TelefonoAsegurado
    ,	Cast(a.CodigoComuna as varchar)  as CodigoComuna
    ,	b.z07310DSC as DescripcionComuna
    ,	Cast(a.CodigoCiudad as varchar)as CodigoCiudad
    ,	Cast(a.DescripcionCuidad as varchar)as DescripcionCuidad
    ,	Cast(a.CodigoRegion as varchar) as CodigoRegion
    ,	c.z07339DSC as DescripcionRegion
    ,	a.VigenciaDesde
    ,	a.VigenciaHasta
    ,	Cast(d.totcuotas as varchar)as CuotasPactadas
    , null  as   PrimaNetaCaja
    , case
    				when (d.totcuotas between 3 and 24)then  (0.6700)
    				when (d.totcuotas between 25 and 36)then (1.2100)
    				when (d.totcuotas between 37 and 60)then (1.7100)
            else 0
    
    	end as PrimaBrutaCaja 
    into	dbo.ing_produccion_ant	
    FROM         dbo.ing_produccion_temp_ant AS a LEFT OUTER JOIN
                          dbo.z07310 AS b ON a.codigocomuna = b.z07310COM LEFT OUTER JOIN
                          dbo.z07339 AS c ON a.CodigoRegion = c.z07339REG CROSS JOIN
                          dbo.Produccion_Cuotas_ant AS d
    WHERE     (a.NroCredito = d.ppoper) AND (a.RutAsegurado = d.ppcta) AND (a.CODAGEN = d.ppsuc)
END
